#!/usr/bin/env node

/**
 * 批量更新历史记录的节拍字段
 * 使用方法: node batch-update-takt-fields.cjs
 */

const sqlite3 = require('sqlite3').verbose();
const path = require('path');

const DB_PATH = path.join(__dirname, '../app/backend/data/pph.sqlite');
console.log('📍 数据库路径:', DB_PATH);

async function batchUpdateTaktFields() {
  return new Promise((resolve, reject) => {
    const db = new sqlite3.Database(DB_PATH);
    
    console.log('🚀 开始批量更新节拍字段...');
    
    db.serialize(() => {
      // 1. 检查需要更新的记录数量
      const checkSql = "SELECT COUNT(*) as total FROM pph_records WHERE target_takt_time IS NULL OR target_motor_takt_time IS NULL";
      console.log('🔍 执行查询SQL:', checkSql);
      db.get(checkSql, (err, row) => {
        if (err) {
          console.error('❌ 查询失败:', err);
          return reject(err);
        }
        
        console.log(`📊 需要更新的记录数量: ${row.total}`);
        
        if (row.total === 0) {
          console.log('✅ 所有记录的节拍字段都已更新');
          db.close();
          return resolve();
        }
        
        // 2. 批量更新节拍字段
        const updateSql = `
          UPDATE pph_records 
          SET 
            target_takt_time = (
              SELECT takt_time 
              FROM pph_targets 
              WHERE pph_targets.material_code = pph_records.material_code 
                AND pph_targets.line_name = pph_records.line_name 
                AND pph_targets.record_date <= pph_records.record_date 
              ORDER BY pph_targets.record_date DESC 
              LIMIT 1
            ),
            target_motor_takt_time = (
              SELECT motor_takt_time 
              FROM pph_targets 
              WHERE pph_targets.material_code = pph_records.material_code 
                AND pph_targets.line_name = pph_records.line_name 
                AND pph_targets.record_date <= pph_records.record_date 
              ORDER BY pph_targets.record_date DESC 
              LIMIT 1
            )
          WHERE target_takt_time IS NULL OR target_motor_takt_time IS NULL
        `;
        
        db.run(updateSql, function(err) {
          if (err) {
            console.error('❌ 更新失败:', err);
            return reject(err);
          }
          
          console.log(`✅ 成功更新 ${this.changes} 条记录的节拍字段`);
          
          // 3. 验证更新结果
          db.get(`
            SELECT COUNT(*) as updated 
            FROM pph_records 
            WHERE target_takt_time IS NOT NULL OR target_motor_takt_time IS NOT NULL
          `, (err, row) => {
            if (err) {
              console.error('❌ 验证失败:', err);
              return reject(err);
            }
            
            console.log(`📈 已有节拍数据的记录数量: ${row.updated}`);
            
            // 4. 显示样本数据
            db.all(`
              SELECT material_code, line_name, record_date, 
                     target_takt_time, target_motor_takt_time 
              FROM pph_records 
              WHERE target_takt_time IS NOT NULL OR target_motor_takt_time IS NOT NULL 
              LIMIT 5
            `, (err, rows) => {
              if (err) {
                console.error('❌ 查询样本失败:', err);
                return reject(err);
              }
              
              console.log('\n📋 样本数据:');
              console.table(rows);
              
              db.close();
              resolve();
            });
          });
        });
      });
    });
  });
}

// 执行更新
batchUpdateTaktFields()
  .then(() => {
    console.log('\n🎉 批量更新完成！');
    process.exit(0);
  })
  .catch((error) => {
    console.error('\n❌ 批量更新失败:', error);
    process.exit(1);
  });
